﻿using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TCOChart
{
    //实体与数据库结构一样
    public class Student
    {
        //数据是自增需要加上IsIdentity
        //数据库是主键需要加上IsPrimaryKey
        //注意：要完全和数据库一致2个属性
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }

        public int? SchoolId { get; set; }
        public string? Name { get; set; }
        public string? sex { get; set; }
        public string? age { get; set; }
    }

    public class SqlSugarHelper
    {
        private string FilePath = Environment.CurrentDirectory + @"\SqlSugarDB\SugarSQL.db";
        private SqlSugarClient db = null;

        public SqlSugarHelper()
        {
            GetSql();
        }

        /// <summary>
        /// 获得数据库对象
        /// </summary>
        /// <returns></returns>
        public void GetSql()
        {
            db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = $"Data Source={FilePath}", // SQLite 连接字符串
                DbType = DbType.Sqlite, // 指定数据库类型为 SQLite
                IsAutoCloseConnection = true, // 自动关闭连接
                InitKeyType = InitKeyType.Attribute//从实体特性中读取主键自增列信息
            });
        }
        /// <summary>
        /// 创建数据库
        /// </summary>
        public bool CreateBase()
        {
            return db.DbMaintenance.CreateDatabase();
        }
        /// <summary>
        /// 创建数据表
        /// </summary>
        public void CreateTable()
        {
            //两种方法都能创建表
            //db.CodeFirst.InitTables(typeof(Student));
            db.CodeFirst.InitTables<Student>();
        }

        
        /// <summary>
        /// 查询一个表全部数据
        /// </summary>
        /// <returns></returns>
        public List<Student> QueryAll()
        {
            //查询表的所有
            return db.Queryable<Student>().ToList();
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="a"></param>
        /// <param name="b"></param>
        /// <param name="c"></param>
        public void Query(out Student a, out List<Student> b, out List<Student> c)
        {
            // 跟据主键查询：id为参数
            int id = 2;
            a = db.Queryable<Student>().InSingle(id);

            //根据某个数据查询：name为方法参数
            string name = "Tom";
            b = db.Queryable<Student>().Where(s => s.Name == name).ToList();

            //模糊查询：方法参数key为关键字
            string key = "o";
            c = db.Queryable<Student>().Where(s => s.Name.Contains(key)).ToList();
        }
        /// <summary>
        /// 插入数据
        /// </summary>
        public void InsertData()
        {
            var Tom = new Student()
            {
                Id = 2,
                SchoolId = 3,
                Name = "Tom"
            };
            db.Insertable(Tom).ExecuteCommand();
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        public void UpdateData()
        {
            var Tom = new Student()
            {
                Id = 2,
                SchoolId = 3505,
                Name = "Tom"
            };
            db.Updateable(Tom).ExecuteCommand();
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        public void DeleteData()
        {
            //根据主键
            db.Deleteable<Student>().Where(it => it.Id == 1).ExecuteCommand();
            //根据主键数组
            db.Deleteable<Student>().In(new int[] { 1, 2 }).ExecuteCommand();
        }
    }
}
